package com.raja.servingStation.dao;

import com.alibaba.fastjson.JSONObject;
import com.raja.servingStation.common.constant.Constants;
import com.raja.servingStation.common.constant.MysqlPageVo;
import com.raja.servingStation.datasources.RepairSql2oTemp;
import com.raja.servingStation.entity.RepairOrderResp;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.sql2o.Connection;
import org.sql2o.Query;

import java.util.List;


/**
 * on 2017/10/10.
 */
@Repository
public class HistoryOrderDao {
    private static final Logger log = LoggerFactory.getLogger(HistoryOrderDao.class);

    @Autowired
    private RepairSql2oTemp sql2oTemp;




    /**
     * 历史工单列表总数（维修）
     * @return
     */
    public Long getRepairOrderCount(JSONObject body,Integer stationId) {
        String sql ="select count(t1.ORDER_ID) from t_repair_order t1 left join t_car t2 on t1.CAR_ID = t2.ID    " +
                "where t1.REPAIR_TYPE = 2 and t1.REPAIR_ID = :stationId " ;
        if(body.getString("keyWord") != null) {
            sql = sql +"and (instr(t1.ORDER_CODE ,:keyWord) > 0 or instr(t1.CAR_NO ,:keyWord) > 0 or instr(t2.COMPANY_NAME ,:keyWord)>0 ) ";
        }
        if(body.getString("payType") != null) {
            sql = sql +"and instr(t1.SETTLEMENT_TYPE ,:payType)>0";
        }
        log.debug(sql);
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql);
            query.addParameter("stationId",stationId);
            if(body.getString("keyWord") != null) {
                query.addParameter("keyWord", body.getString("keyWord"));
            }
            if(body.getString("payType") != null) {
                query.addParameter("payType", body.getString("payType"));
            }
            return (Long)query.executeScalar();
        }
    }

    /**
     * 历史工单列表总数（首保）
     * @return
     */
    public Long getFirstInsuranceOrderCount(JSONObject body,Integer stationId) {
        String sql = "select count(t1.FIO_ID)  from t_first_insurance_order t1 left join t_car t2 on t1.CAR_ID = t2.ID where t1.REPAIR_ID = :stationId ";
        if(body.getString("keyWord") != null) {
            sql = sql +"and (instr(t1.ORDER_CODE ,:keyWord) > 0 or instr(t1.CAR_NO ,:keyWord) > 0 or instr(t2.COMPANY_NAME ,:keyWord)>0) ";
        }
        if(body.getString("payType") != null) {
            sql = sql +"and instr(t1.SETTLEMENT_TYPE ,:payType)>0";
        }
        log.debug(sql);
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql);
            query.addParameter("stationId",stationId);
            if(body.getString("keyWord") != null) {
                query.addParameter("keyWord", body.getString("keyWord"));
            }
            if(body.getString("payType") != null) {
                query.addParameter("payType", body.getString("payType"));
            }
            return (Long)query.executeScalar();
        }
    }


    /**
     * 历史工单列表 List（维修）
     * @param pageVO
     * @return
     * @throws Exception
     */
    public List<RepairOrderResp> getRepairOrderList(JSONObject body,MysqlPageVo pageVO,Integer stationId) throws Exception {
        String sql = " select t1.ORDER_ID as orderId,t1.ORDER_CODE as orderCode,t1.CAR_NO as carNo,t2.COMPANY_NAME as carCompany,t1.DRIVER_PHONE as driverPhone,t1.DRIVER_NAME as driverName,t1.UPDATE_TIME as overTime,count(t3.COMPONENT_PRICE) as money,t1.SETTLEMENT_TYPE as settlementType,t4.FAULT_LABLE as fault " +
                " from t_repair_order t1  left join t_car t2 on t1.CAR_ID = t2.ID left join t_repair_plan t3 on t1.ORDER_ID = t3.ORDER_ID  " +
                " left join t_fault_information t4 on t1.ORDER_ID = t4.ORDER_ID  where t1.REPAIR_TYPE = 2 and t1.REPAIR_ID = :stationId ";
        if(body.getString("keyWord") != null) {
            sql = sql +"and (instr(t1.ORDER_CODE ,:keyWord) > 0 or instr(t1.CAR_NO ,:keyWord) > 0 or instr(t2.COMPANY_NAME ,:keyWord)>0 ) ";
        }
        if(body.getString("payType") != null) {
            sql = sql +"and instr(t1.SETTLEMENT_TYPE ,:payType)>0";
        }
        String sqlfy = Constants.mysql_page_pre_sql+sql+Constants.mysql_page_end_sql;
        log.debug(sql);
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sqlfy);
            query.addParameter("limit",pageVO.getPageSize().intValue());
            query.addParameter("offset",pageVO.getOffset().intValue());
            query.addParameter("stationId",stationId);
            if(body.getString("keyWord") != null) {
                query.addParameter("keyWord", body.getString("keyWord"));
            }
            if(body.getString("payType") != null) {
                query.addParameter("payType", body.getString("payType"));
            }
            List<RepairOrderResp> repairOrders = query.setAutoDeriveColumnNames(true).executeAndFetch(RepairOrderResp.class);
            return repairOrders;
        }
    }

    /**
     * 历史工单列表 List（首保）
     * @param pageVO
     * @return
     * @throws Exception
     */
    public List<RepairOrderResp> getFirstInsuranceOrderList(JSONObject body,MysqlPageVo pageVO,Integer stationId) throws Exception {
        String sql = " select t1.FIO_ID as orderId,t1.ORDER_CODE as orderCode,t1.CAR_NO as carNo,t2.COMPANY_NAME as carCompany," +
                "t1.USER_PHONE as driverPhone,t1.USER_NAME as driverName,t1.UPDATE_TIME as overTime,t1.SETTLEMENT_TYPE as settlementType " +
                "from t_first_insurance_order t1  left join t_car t2 on t1.CAR_ID = t2.ID   where t1.REPAIR_ID = :stationId ";
        if(body.getString("keyWord") != null) {
            sql = sql +"and (instr(t1.ORDER_CODE ,:keyWord) > 0 or instr(t1.CAR_NO ,:keyWord) > 0 or instr(t2.COMPANY_NAME ,:keyWord)>0) ";
        }
        if(body.getString("payType") != null) {
            sql = sql +"and instr(t1.SETTLEMENT_TYPE ,:payType)>0";
        }
        String sqlfy = Constants.mysql_page_pre_sql+sql+Constants.mysql_page_end_sql;
        log.debug(sql);
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sqlfy);
            query.addParameter("limit",pageVO.getPageSize().intValue());
            query.addParameter("offset",pageVO.getOffset().intValue());
            query.addParameter("stationId",stationId);
            if(body.getString("keyWord") != null) {
                query.addParameter("keyWord", body.getString("keyWord"));
            }
            if(body.getString("payType") != null) {
                query.addParameter("payType", body.getString("payType"));
            }
            List<RepairOrderResp> firstInsuranceOrders = query.setAutoDeriveColumnNames(true).executeAndFetch(RepairOrderResp.class);
            return firstInsuranceOrders;
        }
    }


}
